<?php
/**
 * Description of BaseDBGridDraw
 *
 * @author fusq
 */
abstract class BaseDBGridDraw {


    protected  $_db;

    protected  $_prefix;

    protected  $_baseUrl = "";

    protected  $_itemActions = null;

    protected $_module = "";

    protected $_view;

    protected $_post;

    protected $_moduleActions;

    protected $_filterAction;

    protected $_colActions;

    protected $_columns;

    protected $_filters = array();

    protected $_valueFitlers = array();

    protected $_advanceFilters = array();

    protected $_selectOptionFilter = array();

    protected $_tables;

    protected $_primaryKey = "id";

    protected $_defaultSortOrder = "ASC";

    protected $_sortColumn = "";

    protected $_pageTitle = "";

    protected $_pageTitleLink = "";

    protected $_translate;

    protected $pageOptions = array(5 => "5" , 15 => "15" , 50 => "50" , 80 => "80");

    protected $dbFilter;

    protected $paginator;

    protected $_isExport = false;

    protected $_exportFileName = "";

    protected $_returnData = false;

    protected $_filterColNum = 0;

    protected $_callBackMethod = "";

    public function  __construct($view) {
        $this->_db = Zend_Registry::get("db_r");
        $this->_prefix = Zend_Registry::get("db_table_prefix");
        $this->_baseUrl = Zend_Controller_Front::getInstance()->getBaseUrl();
        $this->_view = $view;
        $this->dbFilter = new DBGridFilter($this->_view);
        $this->_translate = Zend_Registry::get('Zend_Translate');
    }

    public function setTables($tables){
         $this->_tables = $tables;
    }

     public function addTable($table,$cols,$condition = null,$joinType = DBGridConstant::JOIN_TYPE_INNER){
         if(is_array($cols)){
             $colsPara = $cols;
         }else{
             $colsPara = array($cols);
         }
         $this->_tables[] = array("table" => $table,"cols" => $colsPara , "condition" => $condition,"joinType" => $joinType);
     }

    public function setFilters($filters){
        $this->_filters = $filters;
    }

    public function addFilter($type,$name,$label,$attr = array(),$operator=DBGridConstant::OPERATOR_LIKE_A){
         $this->_filters[] = array("type"=>$type,
                                   "name"=>$name,
                                   "label"=>$label,
                                   "attr"=>$attr,
                                   "operator" => $operator,
                                   "options" => array());
     }

    public function addAdvanceFilter($type,$name,$label,$attr = array(),$operator=DBGridConstant::OPERATOR_LIKE_A){
         $this->_advanceFilters[] = array("type"=>$type,
                                   "name"=>$name,
                                   "label"=>$label,
                                   "attr"=>$attr,
                                   "operator" => $operator,
                                   "options" => array());
     }

    public function addOptionsFilter($type,$name,$label,$options=array(),$attr = array(),$operator = DBGridConstant::OPERATOR_EQUAL,$sql = ""){
         $this->_filters[] = array("type"=>$type,
                                   "name"=>$name,
                                   "label"=>$label,
                                   "attr"=>$attr,
                                   "options" => $options,
                                   "operator" => $operator,
                                   "sql" => $sql);
     }

     public function addAdvanceOptionsFilter($type,$name,$label,$options=array(),$attr = array()){
         $this->_advanceFilters[] = array("type"=>$type,
                                   "name"=>$name,
                                   "label"=>$label,
                                   "attr"=>$attr,
                                   "operator" => DBGridConstant::OPERATOR_EQUAL,
                                   "options" => $options);
     }

     public function addValueFilter($name,$value,$operator=DBGridConstant::OPERATOR_EQUAL){
        $this->_valueFitlers[] = array("type"=> DBGridConstant::FILTER_TYPE_VALUE ,
                                  "name"=>$name,
                                  "value"=>$value,
                                  "operator" => $operator);
     }

     public function addSelectOptionFilter($name,$label,$operator){
         $this->_selectOptionFilter[$name] = array(
                                            "name"=>$name,
                                            "label"=>$label,
                                            "operator" => $operator
                                        );
     }

    public function setColumns($columns){
        $this->_columns = $columns;
    }

    public function addColumn($name,$label,$action = "",$valueLabel = array(),$sortKey = "",$methodType = DBGridConstant::CALL_SYSTEM_METHOD , $method = "",$visable=true){
         $column = array(
             "name" => $name,
             "label" => $label,
             "action"=> $action,
             "sort" => empty($sortKey) ? $name : $sortKey,
             "valueLabel" => $valueLabel,
             "methodType" => $methodType,
             "method" => $method,
             "visable" => $visable
         );
         $this->_columns[] = $column;
     }

    public function setItemActions($itemActions){
        $this->_itemActions = $itemActions;
    }

    public function setModule($module){
         $this->_module = $module;
    }

    public function setFilterAction($filterAction){
         $this->_filterAction = $filterAction;
    }

    public function setModuleAction($moduleActions){
         $this->_moduleActions = $moduleActions;
    }

    public function addModuleAction($name,$label,$action,$params=array(),$methodType = DBGridConstant::CALL_SYSTEM_METHOD , $method = ""){
          $this->_moduleActions[] = array(
                                        "name" => $name,
                                        "label" => $label,
                                        "action" => $action,
                                        "params" => $params,
                                        "methodType" => $methodType,
                                        "method" => $method
                                    );
     }

    public function setPost($post){
         $this->_post = $post;
    }

     public function setSortOrder($col,$order = 'ASC'){
         $this->_defaultSortOrder = $order;
         $this->_sortColumn = $col;
     }
     
    public function setPageTitle($pageTitle,$pageTitleLink){
        $this->_pageTitle = $pageTitle;
        $this->_pageTitleLink = $pageTitleLink;
    }

    public function setIsExport($isExport){
        $this->_isExport = $isExport;
    }

    public function setExprotFileName($fileName){
            $this->_exportFileName = $fileName;
    }

    public function setPrimaryKey($key){
         $this->_primaryKey = $key;
    }

    public function setReturnData($returnData){
        $this->_returnData = $returnData;
    }

    public function setCallBackMethod($callBackMethod){
        $this->_callBackMethod = $callBackMethod;
    }

    public function setFilterColNum($filterColNum){
            $this->_filterColNum = $filterColNum;
    }

    public function addColAction($name,$action,$label,$image){
            $this->_colActions[] = array(
            "name" => $name,
            "action" => $action,
            "label" => $label,
            "image" => $image);
    }

    protected function escape($value){
         return $this->_view->escape($value);
    }
    

    /**
      * translate sql
      */
    protected function translateDbSelect(){
         $this->getOrder();
         $select = $this->_db->select();
         foreach($this->_tables as $table){
             if(!isset($table['condition'])){
                $select->from($this->_prefix . $table['table'],$table['cols']);
             }else{
             	$joinType = $table['joinType'];
                if($joinType == DBGridConstant::JOIN_TYPE_INNER){
                    $select->join($this->_prefix . $table['table'],$table['condition'],$table['cols']);
                }else if($joinType == DBGridConstant::JOIN_TYPE_OUTER){
                    $select->joinLeft($this->_prefix . $table['table'],$table['condition'],$table['cols']);
                }
             }
         }
         //translate where
         if(!empty($this->_filters) || !empty($this->_advanceFilters) || !empty($this->_valueFitlers)){
             //$allFilters = array_merge($this->_filters,$this->_advanceFilters);
             if(isset($this->_post['advanceSearchStatus']) && $this->_post['advanceSearchStatus'] == 1){
                 $allFilters = array_merge($this->_advanceFilters,$this->_valueFitlers);
             }else{
                 $allFilters = array_merge($this->_filters,$this->_valueFitlers);
             }
             foreach($allFilters as $filter){
                 $type = $filter['type'];
                 if(strpos($filter['name'],"-") === false){
                     $key = $filter['name'];
                 }else{
                     $key = $this->_prefix .str_replace("-", ".", $filter['name']);
                 }
                 $operator = $filter['operator'];
                 if($type == DBGridConstant::FILTER_TYPE_VALUE){
                     $whereCondition = "";
                     $whereValue = "";
                     $value = $filter['value'];
                     if(!is_array($value)){
                        $value = trim($filter['value']);
                     }                     
                     $whereClause = $this->translateWhereCondition($operator, $key, $value);
                     $whereCondition = $whereClause[0];
                     $whereValue = $whereClause[1];
                     if($whereValue != ""){
                         $select->where($whereCondition, $whereValue );
                     }else{
                         $select->where($whereCondition);
                     }
                 }else if($type == DBGridConstant::FILTER_TYPE_DATE){
                     //date filter                     
                     if($operator == DBGridConstant::OPERATOR_BETWEEN){
                         $value1 = isset($this->_post[$filter['name']."-1"]) ? $this->_post[$filter['name']."-1"] : null;
                         $value2 = isset($this->_post[$filter['name']."-2"]) ? $this->_post[$filter['name']."-2"] : null;
                         if(!empty($value1)){
                             $select->where("to_days($key) >= to_days(?) ", $value1 );
                         }
                         if(!empty($value2)){
                             $select->where("to_days($key) <= to_days(?) ", $value2 );
                         }                         
                     }else if($operator == DBGridConstant::OPERATOR_GREATER){
                         $value1 = isset($this->_post[$filter['name']."-1"]) ? $this->_post[$filter['name']."-1"] : null;
                         if(!empty($value1)){
                             $select->where("to_days($key) >= to_days(?) ", $value1 );
                         }
                     }else if($operator == DBGridConstant::OPERATOR_LESS){
                         $value1 = isset($this->_post[$filter['name']."-1"]) ? $this->_post[$filter['name']."-1"] : null;
                         if(!empty($value1)){
                             $select->where("to_days($key) <= to_days(?) ", $value1 );
                         }
                     }
                 } else if($type == DBGridConstant::FILTER_TYPE_BETWEEN){
 //steve start
                           $value1 = isset($this->_post[$filter['name']."-1"]) ? $this->_post[$filter['name']."-1"] : null;
                           $value2 = isset($this->_post[$filter['name']."-2"]) ? $this->_post[$filter['name']."-2"] : null;
                           if(!empty($value1) && is_numeric($value1)){
                             $select->where("$key >= ? ", $value1 );
                           }
                           if(!empty($value2) && is_numeric($value2)){
                             $select->where("$key <= ?", $value2 );
                           } 
                 } else {
                     $value = isset($this->_post[$filter['name']]) ? $this->_post[$filter['name']] : null;
                     if(isset($filter['name']) && isset($value) && trim($value) != ""){
                     	  $sql = "";
                     	  if($filter['operator'] == DBGridConstant::OPERATOR_IN_SQL){
                     	  	 $sql = $filter['sql'];
                     	  }
                          $value = trim($value);
                          $whereCondition = "";
                          $whereValue = "";
                          $whereClause = $this->translateWhereCondition($operator, $key, $value,$sql);
                          $whereCondition = $whereClause[0];
                          $whereValue = $whereClause[1];
                          $select->where($whereCondition, $whereValue );
                     }
                 }
             }
         }
         //translate select option filters
         if(!isset($this->_post['advanceSearchStatus']) || $this->_post['advanceSearchStatus'] == 0){
             if(!empty($this->_selectOptionFilter) && isset($this->_post['selectOptionFilter'])){
                  $key = $this->_post['selectOptionFilter'];
                  $operator = $this->_selectOptionFilter[$key]['operator'];
                  $value = isset($this->_post['selectOptionFilterValue']) ? trim($this->_post['selectOptionFilterValue']) : "";
                  if(strpos($key,"-") === false){
                  }else{
                     $key = $this->_prefix .str_replace("-", ".", $key);
                  }
                  if($value != ""){
                        $whereCondition = "";
                        $whereValue = "";
                        $whereClause = $this->translateWhereCondition($operator, $key, $value);
                        $whereCondition = $whereClause[0];
                        $whereValue = $whereClause[1];
                        $select->where($whereCondition, $whereValue );
                  }
             }
         }
         //sort
         $sort = "";
         if(!empty($this->_sortColumn)){
             if(strpos($this->_sortColumn,"-") === false){
                    $sort = $this->_sortColumn . " " . $this->_defaultSortOrder ;
             }else{
                    $sort = str_replace("-", ".", $this->_sortColumn) . " " .  $this->_defaultSortOrder;
             }
         }
         if(!empty($sort)){
             $select->order($sort);
         }
//        echo $select->__toString();
//        exit();
         return $select;
     }
     
    protected function translateWhereCondition($operator,$key,$value,$sql = ""){
          $whereCondition = "";
          $whereValue = "";
          switch($operator){
             case DBGridConstant::OPERATOR_EQUAL:
                  $whereCondition = $key . " = ? ";
                  $whereValue = $value;
                  break;
             case DBGridConstant::OPERATOR_NOT_EQUAL:
                  $whereCondition = $key . " <> ? ";
                  $whereValue = $value;
                  break;
             case DBGridConstant::OPERATOR_LESS:
                  $whereCondition = $key . " < ? ";
                  $whereValue = $value;
                  break;
             case DBGridConstant::OPERATOR_GREATER:
                  $whereCondition = $key . " > ? ";
                  $whereValue = $value;
                  break;
             case DBGridConstant::OPERATOR_NULL:
                  $whereCondition = $key . " is null ";
                  break;
             case DBGridConstant::OPERATOR_NOT_NULL:
                  $whereCondition = $key . " is not null ";
                  break;
             case DBGridConstant::OPERATOR_LIKE_A:
                  $whereCondition = $key . " like ? ";
                  $whereValue = "%" . $value . "%";
                  break;
             case DBGridConstant::OPERATOR_LIKE_L:
                  $whereCondition = $key . " like ? ";
                  $whereValue = "%" . $value;
                  break;
             case DBGridConstant::OPERATOR_LIKE_R:
                  $whereCondition = $key . " like ? ";
                  $whereValue = $value . "%";
                  break;
             case DBGridConstant::OPERATOR_IN:
                  $inValue = implode(",", $value);
                  $whereCondition = $key . " in ($inValue) ";
                  break;
             case DBGridConstant::OPERATOR_IN_SQL:
                  $whereValue = $value;
                  $whereCondition = $sql;             	
             	  break;
          }

          return array($whereCondition,$whereValue);
    }

    /**
     * set sort order
     */
    protected function getOrder(){
        if(!empty($this->_post['sortCol']) && !empty($this->_post['sortOrder'])){
            $this->setSortOrder(trim($this->_post['sortCol']), trim($this->_post['sortOrder']));
        }
    }

    /**
     *  export data
     */
    public function exportData(){
         $columns = $this->_columns;
         if(!empty($columns)){
             $filename = "Export-" . (empty($this->_exportFileName) ? "" : $this->_exportFileName . "-") . date('dmY') . ".csv";
             $select = $this->translateDbSelect();
             $sql = $select->__toString();
             $items = $this->_db->fetchAll($sql);
             //init csv file
             header("Pragma: public"); 
             header("Expires: 0");
             header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
             header("Cache-Control: private",false);             
             header("Content-type:application/vnd.ms-excel");
             header("Content-Disposition:filename=" . $filename);
             //init header
             $header = "";
             foreach($columns as $col){
                 $header .= $this->escape($col['label']) . "," ;
             }
             $start = strrpos($header,",");
             $header = substr_replace($header,"\n", $start , 1);
             echo $header;
             //init data           
             foreach($items as $item){
                $row = "";
                foreach ($columns as $col){
                    $name = $col['name'];
                    $valueLabel = $col['valueLabel'];
                    if(!empty($valueLabel)){
                        $valueLabelKey = $this->escape($item[$name]);
                        $row .= $this->escape($valueLabel[$valueLabelKey]) . ",";
                    }else{
                        $row .= $this->escape($item[$name]) . ",";
                    }
                }
                $start = strrpos($row,",");
                $row = substr_replace($row,"\n", $start , 1);
                echo $row;
             }
         }
         exit();
    }
}
